﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DTO;
namespace DAO
{
    public class DAO_DonHang
    {
        public List<DTO_DonHangView> AdLayDSDH()
        {
            List<DTO_DonHangView> dsDonHang = new List<DTO_DonHangView>();
            string query = "SELECT dh.MaDH, km.TenKM, tk.HoTen, dh.SoLuong, km.DonGiaMoi, dh.NgayDatHang, dh.TinhTrang FROM KHUYENMAI km, DONHANG dh, TAIKHOAN tk WHERE km.MaKM = dh.KhuyenMai and tk.MaTK = dh.TaiKhoan";
            DataTable dt = DataProvider.ExecuteQuery(query);
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                DTO_DonHangView donhang = new DTO_DonHangView();
                donhang.Stt = i + 1;
                donhang.MaDH = int.Parse(dr[0].ToString());   //Ma DH
                donhang.TenKM = dr[1].ToString();
                donhang.TenTK = dr[2].ToString();
                donhang.SoLuong = int.Parse(dr[3].ToString());
                donhang.DonGia = int.Parse(dr[4].ToString());
                donhang.NgayDat = dr[5].ToString();
                donhang.TongTien = donhang.SoLuong * donhang.DonGia;
                int b = int.Parse(dr[6].ToString());
                if (b == 1)
                {
                    donhang.TinhTrang = true;
                }
                else donhang.TinhTrang = false;
                dsDonHang.Add(donhang);
                i++;
            }
            return dsDonHang;
        }
        public List<DTO_DonHangView> LayDSDHTheoNguoiDat(String nguoiDat)
        {
            List<DTO_DonHangView> dsDonHang = new List<DTO_DonHangView>();
            string query = "SELECT dh.MaDH, km.TenKM, tk.HoTen, dh.SoLuong, km.DonGiaMoi, dh.NgayDatHang, dh.TinhTrang FROM KHUYENMAI km, DONHANG dh, TAIKHOAN tk WHERE dh.TaiKhoan = " +nguoiDat+ " and km.MaKM = dh.KhuyenMai and tk.MaTK = dh.TaiKhoan";
            DataTable dt = DataProvider.ExecuteQuery(query);
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                DTO_DonHangView donhang = new DTO_DonHangView();
                donhang.Stt = i+1;
                donhang.MaDH = int.Parse(dr[0].ToString());   //Ma DH
                donhang.TenKM = dr[1].ToString();
                donhang.TenTK = dr[2].ToString();
                donhang.SoLuong = int.Parse(dr[3].ToString());
                donhang.DonGia = int.Parse(dr[4].ToString());
                donhang.NgayDat = dr[5].ToString();
                donhang.TongTien = donhang.SoLuong * donhang.DonGia;
                int b = int.Parse(dr[6].ToString());
                if (b == 1)
                {
                    donhang.TinhTrang = true;
                }
                else donhang.TinhTrang = false;
                dsDonHang.Add(donhang);
                i++;
            }
            return dsDonHang;
        }
        public List<DTO_ThongTinDH> LayDSDH()
        {
            List<DTO_ThongTinDH> dsDonHang = new List<DTO_ThongTinDH>();
            string query = "SELECT dh.MaDH, km.TenKM, tk.HoTen, dh.SoLuong, km.DonGiaMoi, dh.NgayDatHang FROM KHUYENMAI km, DONHANG dh, TAIKHOAN tk WHERE km.MaKM = dh.KhuyenMai and tk.MaTK = dh.TaiKhoan";
            DataTable dt = DataProvider.ExecuteQuery(query);
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                DTO_ThongTinDH donhang = new DTO_ThongTinDH();
                donhang.Stt = i+1;
                donhang.MaDH = int.Parse(dr[0].ToString());   //Ma DH
                donhang.TenKM = dr[1].ToString();
                donhang.TenTK = dr[2].ToString();
                donhang.SoLuong = int.Parse(dr[3].ToString());
                donhang.DonGia = int.Parse(dr[4].ToString());
                donhang.NgayDat = dr[5].ToString();
                donhang.TongTien = donhang.SoLuong * donhang.DonGia;
                
                dsDonHang.Add(donhang);
                i++;
            }
            return dsDonHang;
        }
        public List<DTO_ThongTinDH> LayDanhSachDH(int maDN)
        {
            List<DTO_ThongTinDH> dsDonHang = new List<DTO_ThongTinDH>();
            string query = "SELECT dh.MaDH, km.TenKM, tk.HoTen, dh.SoLuong, km.DonGiaMoi, dh.NgayDatHang FROM KHUYENMAI km, DONHANG dh, TAIKHOAN tk WHERE km.DoanhNghiep = " + maDN.ToString() + " and km.MaKM = dh.KhuyenMai and tk.MaTK = dh.TaiKhoan";
            DataTable dt = DataProvider.ExecuteQuery(query);
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                DTO_ThongTinDH donhang = new DTO_ThongTinDH();
                donhang.Stt = i+1;
                donhang.MaDH = int.Parse(dr[0].ToString());   //Ma DH
                donhang.TenKM = dr[1].ToString();
                donhang.TenTK = dr[2].ToString();
                donhang.SoLuong = int.Parse(dr[3].ToString());
                donhang.DonGia = int.Parse(dr[4].ToString());
                donhang.NgayDat = dr[5].ToString();
                donhang.TongTien = donhang.SoLuong * donhang.DonGia;

                dsDonHang.Add(donhang);
                i++;
            }
            return dsDonHang;
        }
		
		public List<DTO_DonHang> SelectAll(string ma)
        {
            List<DTO_DonHang> donhang = new List<DTO_DonHang>();
            string querySelectAll = "SELECT [MaDH], [NguoiNhan], [KhuyenMai], [NgayDatHang], [SoLuong], [PhiGiaoHang], [TinhTrangThanhToan], [HinhThucThanhToan], [GhiChu], [TinhTrang], [HinhThucNhanHang] FROM DONHANG WHERE ([TaiKhoan] = '" + ma + "')";
            DataTable dt = DataProvider.ExecuteQuery(querySelectAll);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_DonHang dto_donhang = new DTO_DonHang();
                dto_donhang.MaDH = int.Parse(dr[0].ToString());
                dto_donhang.NguoiNhan = int.Parse(dr[1].ToString());
                dto_donhang.KhuyenMai = int.Parse(dr[2].ToString());
                dto_donhang.NgayDatHang = Convert.ToDateTime(dr[3].ToString());
                dto_donhang.SoLuong = int.Parse(dr[4].ToString());
                dto_donhang.PhiGiaoHang = int.Parse(dr[5].ToString());
                dto_donhang.TinhTrangThanhToan = int.Parse(dr[6].ToString());
                dto_donhang.HinhThucThanhToan = int.Parse(dr[7].ToString());
                dto_donhang.GhiChu = dr[8].ToString();
                dto_donhang.TinhTrang = int.Parse(dr[9].ToString());
                dto_donhang.HinhThucNhanHang = int.Parse(dr[10].ToString());
                donhang.Add(dto_donhang);
            }
            return donhang;
        }

        public void Insert(DTO_DonHang value)
        {
            string query = "INSERT INTO DONHANG VALUES (" + value.NguoiNhan.ToString() + ", " + value.TaiKhoan.ToString() + ", " + value.KhuyenMai.ToString() + ", '" + value.NgayDatHang.ToString() + "', " + value.SoLuong.ToString() + ", '" + value.PhiGiaoHang + "', " + value.TinhTrangThanhToan.ToString() + ", " + value.HinhThucThanhToan.ToString() + ", N'" +value.GhiChu+"', "+ value.TinhTrang.ToString() + ", " + value.HinhThucNhanHang.ToString() + ")";
            DataProvider.ExecuteQuery(query);
        }

        public void Delete(int ma)
        {
            string query = "DELETE FROM DONHANG WHERE [MaDH]=" + ma.ToString();
            DataProvider.ExecuteQuery(query);
        }
        public DTO_DonHang GetThongTinDH(string ma)
        {
            DTO_DonHang donhang = new DTO_DonHang();
            string querySelectAll = "SELECT [MaDH], [NguoiNhan], [TaiKhoan], [KhuyenMai], [NgayDatHang], [SoLuong], [PhiGiaoHang], [TinhTrangThanhToan], [HinhThucThanhToan], [GhiChu], [TinhTrang] FROM DONHANG WHERE ([MaDH] = '" + ma + "')";
            DataTable dt = DataProvider.ExecuteQuery(querySelectAll);
            foreach (DataRow dr in dt.Rows)
            {
                donhang.MaDH = int.Parse(dr[0].ToString());
                donhang.NguoiNhan = int.Parse(dr[1].ToString());
                donhang.TaiKhoan = int.Parse(dr[2].ToString());
                donhang.KhuyenMai = int.Parse(dr[3].ToString());
                donhang.NgayDatHang = Convert.ToDateTime(dr[4].ToString());
                donhang.SoLuong = int.Parse(dr[5].ToString());
                donhang.PhiGiaoHang = int.Parse(dr[6].ToString());
                donhang.TinhTrangThanhToan = int.Parse(dr[7].ToString());
                donhang.HinhThucThanhToan = int.Parse(dr[8].ToString());
                donhang.GhiChu = dr[9].ToString();
                donhang.TinhTrang = int.Parse(dr[10].ToString());
            }
            return donhang;
        }
        public DTO_DonHang SelectDH(string ma)
        {
            DTO_DonHang donhang = new DTO_DonHang();
            string querySelectAll = "SELECT [MaDH], [NguoiNhan], [KhuyenMai], [NgayDatHang], [SoLuong], [PhiGiaoHang], [TinhTrangThanhToan], [HinhThucThanhToan], [GhiChu], [TinhTrang] FROM DONHANG WHERE ([MaDH] = '" + ma + "')";
            DataTable dt = DataProvider.ExecuteQuery(querySelectAll);
            foreach (DataRow dr in dt.Rows)
            {
                donhang.MaDH = int.Parse(dr[0].ToString());
                donhang.NguoiNhan = int.Parse(dr[1].ToString());
                donhang.KhuyenMai = int.Parse(dr[2].ToString());
                donhang.NgayDatHang = Convert.ToDateTime(dr[3].ToString());
                donhang.SoLuong = int.Parse(dr[4].ToString());
                donhang.PhiGiaoHang = int.Parse(dr[5].ToString());
                donhang.TinhTrangThanhToan = int.Parse(dr[6].ToString());
                donhang.HinhThucThanhToan = int.Parse(dr[7].ToString());
                donhang.GhiChu = dr[8].ToString();
                donhang.TinhTrang = int.Parse(dr[9].ToString());
            }
            return donhang;
        }
        public void UpdateTinhTrangThanhToan(int ma, int value)
        {
            string query = "UPDATE DONHANG SET TinhTrangThanhToan=" + value + " WHERE ([MaDH] = " + ma + ")";
            DataProvider.ExecuteQuery(query);
        }

        public List<DTO_DonHang1> DoanhThu(string year, string thang)
        {
            List<DTO_DonHang1> list = new List<DTO_DonHang1>();
            string query = "select KHUYENMAI.TenKM,KHUYENMAI.DonGiaMoi,DONHANG.KhuyenMai,DONHANG.NgayDatHang,month(DONHANG.NgayDatHang)as Thang,year(DONHANG.NgayDatHang)as Nam,Count(DONHANG.KhuyenMai) as SoLuong";
            query += " from DONHANG,KHUYENMAI ";
            query += " where DONHANG.KhuyenMai=KHUYENMAI.MaKM and  month(DONHANG.NgayDatHang)=" + thang + " and year(DONHANG.NgayDatHang)=" + year;
            query += "Group By DONHANG.KhuyenMai,KHUYENMAI.TenKM,KHUYENMAI.DonGiaMoi,DONHANG.NgayDatHang,month(DONHANG.NgayDatHang),year(DONHANG.NgayDatHang)";
            query += "order by DONHANG.NgayDatHang asc";
            DataTable dt = DataProvider.ExecuteQuery(query);
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                DTO_DonHang1 donhang = new DTO_DonHang1();
                donhang.TenKM = dr["TenKM"].ToString();
                donhang.DonGiaMoi = Convert.ToInt32(dr["DonGiaMoi"]);
                donhang.KhuyenMai = Convert.ToInt32(dr["KhuyenMai"]);
                donhang.NgayDatHang = Convert.ToDateTime(dr["NgayDatHang"]);
                donhang.SoLuong = Convert.ToInt32(dr["SoLuong"]);
                donhang.Thang = Convert.ToInt32(dr["Thang"]);
                donhang.Nam = Convert.ToInt32(dr["Nam"]);
                donhang.Tien = donhang.DonGiaMoi * donhang.SoLuong;
                list.Add(donhang);
                i++;
            }
            return list;
        }
        public List<float> DoanhThuTungThang(string year)
        {
            List<DTO_DonHang1> list = new List<DTO_DonHang1>();
            List<float> list1 = new List<float>();
            for (int i = 0; i < 12; i++)
                list1.Add(0);
            string query = "select KHUYENMAI.TenKM,KHUYENMAI.DonGiaMoi,DONHANG.KhuyenMai,DONHANG.NgayDatHang,month(DONHANG.NgayDatHang)as Thang,year(DONHANG.NgayDatHang)as Nam,Count(DONHANG.KhuyenMai) as SoLuong";
            query += " from DONHANG,KHUYENMAI ";
            query += " where DONHANG.KhuyenMai=KHUYENMAI.MaKM and year(DONHANG.NgayDatHang)= " + year;
            query += "Group By DONHANG.KhuyenMai,KHUYENMAI.TenKM,KHUYENMAI.DonGiaMoi,DONHANG.NgayDatHang,month(DONHANG.NgayDatHang),year(DONHANG.NgayDatHang)";
            query += "order by DONHANG.NgayDatHang asc";
            DataTable dt = DataProvider.ExecuteQuery(query);
            foreach (DataRow dr in dt.Rows)
            {
                DTO_DonHang1 donhang = new DTO_DonHang1();
                donhang.DonGiaMoi = Convert.ToInt32(dr["DonGiaMoi"]);
                donhang.SoLuong = Convert.ToInt32(dr["SoLuong"]);
                donhang.Thang = Convert.ToInt32(dr["Thang"]);
                list.Add(donhang);
                list1[donhang.Thang - 1] += donhang.DonGiaMoi * donhang.SoLuong;
            }
            return list1;
        }
        public List<DTO_DonHang1> DoanhThuDoanhNghiep(string year, string thang,int MaDN,int code)
        {
            List<DTO_DonHang1> list = new List<DTO_DonHang1>();
            string query="";
            if (code == 1)
            {
                query += "select KHUYENMAI.TenKM,KHUYENMAI.DonGiaMoi,DONHANG.KhuyenMai,DONHANG.NgayDatHang,month(DONHANG.NgayDatHang)as Thang,year(DONHANG.NgayDatHang)as Nam,Count(DONHANG.KhuyenMai) as SoLuong";
                query += " from DONHANG,KHUYENMAI ";
                query += " where DONHANG.KhuyenMai=KHUYENMAI.MaKM and  month(DONHANG.NgayDatHang)=" + thang + " and year(DONHANG.NgayDatHang)=" + year + " and KHUYENMAI.DoanhNghiep=" + MaDN.ToString();
                query += "Group By DONHANG.KhuyenMai,KHUYENMAI.TenKM,KHUYENMAI.DonGiaMoi,DONHANG.NgayDatHang,month(DONHANG.NgayDatHang),year(DONHANG.NgayDatHang)";
                query += "order by DONHANG.NgayDatHang asc";
            }
            else
            {
                query += "select KHUYENMAI.TenKM,KHUYENMAI.DonGiaMoi,DONHANG.KhuyenMai,DONHANG.NgayDatHang,month(DONHANG.NgayDatHang)as Thang,year(DONHANG.NgayDatHang)as Nam,Count(DONHANG.KhuyenMai) as SoLuong";
                query += " from DONHANG,KHUYENMAI ";
                query += " where KHUYENMAI.DoanhNghiep=" + MaDN.ToString() + " and year(DONHANG.NgayDatHang)=" + year;
                query += "Group By DONHANG.KhuyenMai,KHUYENMAI.TenKM,KHUYENMAI.DonGiaMoi,DONHANG.NgayDatHang,month(DONHANG.NgayDatHang),year(DONHANG.NgayDatHang)";
                query += "order by DONHANG.NgayDatHang asc";
            }
            DataTable dt = DataProvider.ExecuteQuery(query);
            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                DTO_DonHang1 donhang = new DTO_DonHang1();
                donhang.TenKM = dr["TenKM"].ToString();
                donhang.DonGiaMoi = Convert.ToInt32(dr["DonGiaMoi"]);
                donhang.KhuyenMai = Convert.ToInt32(dr["KhuyenMai"]);
                donhang.NgayDatHang = Convert.ToDateTime(dr["NgayDatHang"]);
                donhang.SoLuong = Convert.ToInt32(dr["SoLuong"]);
                donhang.Thang = Convert.ToInt32(dr["Thang"]);
                donhang.Nam = Convert.ToInt32(dr["Nam"]);
                donhang.Tien = donhang.DonGiaMoi * donhang.SoLuong;
                list.Add(donhang);
                i++;
            }
            return list;
        }

        public bool AdUpdateDH(DTO_DonHang dh)
        {
            string query = "UPDATE DONHANG SET TinhTrangThanhToan=" + dh.TinhTrangThanhToan.ToString() 
                + ", HinhThucThanhToan=" + dh.HinhThucThanhToan.ToString()
                + ", GhiChu='" + dh.GhiChu.ToString()
                + "', SoLuong=" + dh.SoLuong.ToString()
                + ", PhiGiaoHang=" + dh.PhiGiaoHang.ToString()
                + ",TinhTrang=" + dh.TinhTrang.ToString() 
                + " WHERE (MaDH = " + dh.MaDH.ToString() + ")";
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool UpdateTinhTrang(DTO_DonHangView dh)
        {
            int i;
            if (dh.TinhTrang == true)
                i = 1;
            else i = 0;
            string query = "UPDATE DONHANG SET TinhTrang=" + i.ToString() + " WHERE ([MaDH] = " + dh.MaDH.ToString() + ")";
            try
            {
                DataProvider.ExecuteQuery(query);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}
